﻿using System;
using System.ComponentModel;
using System.Data.SqlClient;
using QLHS.DTO;

namespace QLHS.DataLayer
{
    public class MonHocDAO
    {
        public BindingList<MonHocDTO> LoadAll()
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = "select * from MONHOC";
            BindingList<MonHocDTO> MonHocTable = new BindingList<MonHocDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _MSMH = Convert.ToInt32(reader["MaSoMH"].ToString());
                        string _TenMH = reader["TenMH"].ToString();
                        int _HeSo = Convert.ToInt32(reader["HeSo"].ToString());
                       
                        MonHocDTO temp = new MonHocDTO
                        {
                            MaSoMH = _MSMH,
                            TenMH = _TenMH,
                            HeSo = _HeSo,
                        };
                        MonHocTable.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
            return MonHocTable;
        }

        public void Delete(MonHocDTO del)
        {
            string sql = string.Format("delete from MONHOC where MaSoMH = {0}", del.MaSoMH);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }            
        }

        public void Insert(MonHocDTO InsertObject)
        {
            string sql = string.Format("insert into MONHOC values (N'{0}', {1})",
                InsertObject.TenMH, InsertObject.HeSo);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Update(MonHocDTO UpdateObject)
        {
            string sql = string.Format("update MONHOC set TenMH = N'{1}' , HeSo = {2} where MaSoMH = {0}", UpdateObject.MaSoMH, UpdateObject.TenMH, UpdateObject.HeSo);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        //public void ChangeKeyValue(string Old_MSMH, string New_MSMH)
        //{
        //    string sql = string.Format("update MonHoc set MSMH = '{0}' where (MSMH = '{1}')", New_MSMH, Old_MSMH);

        //    SqlConnection Cn = new SqlConnection(Helper.ConnectionString);
        //    SqlCommand Cmd = new SqlCommand(sql, Cn);
        //    Cn.Open();
        //    Cmd.ExecuteNonQuery();
        //    Cn.Close();
        //}

        public BindingList<MonHocDTO> GetMonHocDataByPrimaryKey(int MSMH)
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = string.Format("select* from MONHOC where MaSoMH = {0}", MSMH);
            BindingList<MonHocDTO> Result = new BindingList<MonHocDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int _MSMH = Convert.ToInt32(reader["MaSoMH"].ToString());
                        string _TenMH = reader["TenMH"].ToString();
                        int _HeSo = Convert.ToInt32(reader["HeSo"].ToString());

                        MonHocDTO temp = new MonHocDTO
                        {
                            MaSoMH = _MSMH,
                            TenMH = _TenMH,
                            HeSo = _HeSo,
                        };
                        Result.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }   
            return Result;
        }

       
    }
}
